package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.COGSInfo;
import com.eece496.Helpers.GradesInfo;

public class COGS extends Database {
	
	private String COGSName;
	private String division;
	private String startTime;
	private String endTime;
	private String sectionNumber;
	private String courseNumber;
	
	public COGS()
	{
		tableName = "COGS";
	}

	public COGS(String COGSName, String division, String startTime, String endTime, String courseNumber, String sectionNumber)
	{
		tableName = "COGS";
		
		this.COGSName = COGSName;
		this.division = division;
		this.startTime = startTime;
		this.endTime = endTime;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;
	}
	@Override
	public void Insert() throws SQLException {
		
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" +
	    					AddQuotes(COGSName) + ", " +	
	    					AddQuotes(division) + ", " +	
	    					AddQuotes(startTime) + ", " +
	    					AddQuotes(endTime) + ", " +
	       					AddQuotes(courseNumber) + ", " +
	    					AddQuotes(sectionNumber) + 
	    					")";
	    stmt.executeUpdate(sqlString);
	    System.out.println(sqlString);

	}
	
	public void DeleteCourseCOGS(String courseNumber, String sectionNumber) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "DELETE from " + tableName + " WHERE courseNumber = " + AddQuotes(courseNumber) +
				" and sectionNumber= " + AddQuotes(sectionNumber);
		stmt.executeUpdate(sqlString);
		System.out.println(sqlString);
	}

	@Override
	public void Delete(String primaryKey) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub

	}
	
	public ArrayList<COGSInfo> GetCOGSList(String courseNumber, String sectionNumber) throws SQLException{
		
		ArrayList<COGSInfo> list = new ArrayList<COGSInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select * from " + tableName +
				 " where courseNumber = " + AddQuotes(courseNumber) +
				 " and sectionNumber = " + AddQuotes(sectionNumber);

		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next()) {
			COGSInfo info = new COGSInfo();
			info.COGSName = rs.getString("COGSName");
			info.division = rs.getString("division");
			info.startTime = rs.getString("StartTime");
			info.endTime = rs.getString("EndTime");
			list.add(info);
		}
		
		return list;	
	}
	
	public ArrayList<COGSInfo> GetCOGSInfo(String COGSName, String division, String courseNumber, String sectionNumber) throws SQLException{
		
		ArrayList<COGSInfo> list = new ArrayList<COGSInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select * from " + tableName +
				 " where courseNumber = " + AddQuotes(courseNumber) +
				 " and sectionNumber = " + AddQuotes(sectionNumber) +
				 " and COGSName = " + AddQuotes(COGSName) +
				 " and division = " + AddQuotes(division);

		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next()) {
			COGSInfo info = new COGSInfo();
			info.COGSName = rs.getString("COGSName");
			info.division = rs.getString("division");
			info.startTime = rs.getString("StartTime");
			info.endTime = rs.getString("EndTime");
			list.add(info);
		}
		
		return list;	
	}
}